<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=utf-8">
<TITLE>Training statistics</TITLE>
</HEAD>
<BODY>

<STYLE>
A {
	color: black;
}
A:hover {
	text-decoration: underline;
}

TABLE {
	border: 1px solid black;
	border-spacing: 0px;
	border-collapse: collapse;
}

TD {
	font-size: 11;
	font-family: sans-serif;
}

TD.darkback {
	color: white;
}
TD.selected {
	border-left: 3px dotted black;
	border-right: 3px dotted black;
}
DIV {
	position: absolute;
}
IMG.hor {
	position: relative;
	float: left;
}

IMG.ver {
	position: absolute;
	float: left;
}

</STYLE>

Click the column headers to sort by that training type. Click the same column again to reverse sorting.<BR/>
<?php 
error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set("memory_limit","20M");
echo "<B><A HREF=\"training.php?";
if ($_GET['c']) echo "c={$_GET['c']}";
if ($_GET['r']) echo "&r={$_GET['r']}";
if ($_GET['min']) echo "&min={$_GET['min']}";
if ($_GET['yb']) {
	echo "\">Return to red/green version</A></B><BR/><BR/>\n";
} else {
	echo "&yb=1\">Alternate blue/yellow version</A> for red-green weak/Deuteranopic/Protanopic people</B><BR/><BR/>";
} ?>

 
<FORM TYPE="GET" ACTION="training.php" NAME="training">

<?php

include "database.inc.php";
include "track.inc.php";

$sigma = array();

$trainingtypes = array("GENERL","STAMNA","SETPCS","DEFEND","SCORIN","WINGER","SHOOTN","SHRTPS","PLAYMK","GOALTN","THRUPS","DEFPOS","WNGATT","NEW_TRAINING_TYPE_FIXME");

$dbh=connect_db();

$minactive = 0;
$maxactive = 99999;
if (is_numeric($_GET['min'])) $minactive = $_GET['min'];
if (is_numeric($_GET['max'])) $maxactive = $_GET['max'];

echo "Only show leagues with at least <INPUT TYPE=\"TEXT\" NAME=\"min\" VALUE=\"$minactive\" SIZE=\"4\" MAXLENGTH=\"5\"> and at most <INPUT TYPE=\"TEXT\" NAME=\"max\" VALUE=\"$maxactive\" SIZE=\"4\" MAXLENGTH=\"5\"> active teams <INPUT TYPE=\"SUBMIT\" VALUE=\"Click to adjust\"><BR/><BR/>\n\n";

echo "<SPAN STYLE=\"background-color: red; color: white; font-weight: bold\">Red</SPAN> means the percentage is above the global average for that training type, <SPAN STYLE=\"background-color: green; color: white; font-weight: bold\">green</SPAN> below. The more intense the color, the greater the difference.<BR/><BR/>\n\n";

echo "For experts: <A HREF=\"training.php?min={$_GET['min']}&max={$_GET['max']}&dump=1\">click here</A> for a hierarchical clustering using Pearson Correlation. May be slow with low thresholds for minimum size (&lt;100). Note the clear correlation between for example the South-American countries Venezuela, Argentina, Uruguay, Peru, Chile and Bolivia. The tree looks best in Firefox. A background on hierarchical clustering can be found <A HREF=\"http://www.bio.davidson.edu/Courses/CompBio/jas/background.htm\">here</A>.<BR/><BR/>\n\n";

echo "Since 11-11-2005, the distribution of training types is being tracked and graphed. See the country detail pages, or see all countries <A HREF=\"allgraphs.php?sub=tr\">here</A>.<BR/><BR/>\n\n";

$col = -1;
if (isset($_GET['c']) && $_GET['c'] >= 0 && $_GET['c'] <= 12 && $_GET['c'] != 1) {
	$col = $_GET['c'];
} else if ($_GET['c'] == "s") {
	$col = "s";
} else if ($_GET['c'] == "id") {
	$col = "id";
}

$order = "DESC";
if ($_GET['r'])	$order = "ASC";

$globals = mysql_query("SELECT 
sum(activeusers) as activeusers, 
sum(t.t0) as t0, (sum(t.t0) / sum(l.activeusers)) * 100 as p0,

sum(t.t2) as t2, (sum(t.t2) / sum(l.activeusers)) * 100 as p2,
sum(t.t3) as t3, (sum(t.t3) / sum(l.activeusers)) * 100 as p3,
sum(t.t4) as t4, (sum(t.t4) / sum(l.activeusers)) * 100 as p4,
sum(t.t5) as t5, (sum(t.t5) / sum(l.activeusers)) * 100 as p5,
sum(t.t6) as t6, (sum(t.t6) / sum(l.activeusers)) * 100 as p6,
sum(t.t7) as t7, (sum(t.t7) / sum(l.activeusers)) * 100 as p7,
sum(t.t8) as t8, (sum(t.t8) / sum(l.activeusers)) * 100 as p8,
sum(t.t9) as t9, (sum(t.t9) / sum(l.activeusers)) * 100 as p9,
sum(t.t10) as t10, (sum(t.t10) / sum(l.activeusers)) * 100 as p10,
sum(t.t11) as t11, (sum(t.t11) / sum(l.activeusers)) * 100 as p11,
sum(t.t12) as t12, (sum(t.t12) / sum(l.activeusers)) * 100 as p12 
FROM leagues_daily l, training t WHERE l.leagueid = t.leagueid AND l.activeusers >0", $dbh);

$global = mysql_fetch_array($globals);

$minmaxes = mysql_query("SELECT
min((t.t0 / l.activeusers) * 100) AS min0, max((t.t0 / l.activeusers) * 100) AS max0,

min((t.t2 / l.activeusers) * 100) AS min2, max((t.t2 / l.activeusers) * 100) AS max2,
min((t.t3 / l.activeusers) * 100) AS min3, max((t.t3 / l.activeusers) * 100) AS max3,
min((t.t4 / l.activeusers) * 100) AS min4, max((t.t4 / l.activeusers) * 100) AS max4,
min((t.t5 / l.activeusers) * 100) AS min5, max((t.t5 / l.activeusers) * 100) AS max5,
min((t.t6 / l.activeusers) * 100) AS min6, max((t.t6 / l.activeusers) * 100) AS max6,
min((t.t7 / l.activeusers) * 100) AS min7, max((t.t7 / l.activeusers) * 100) AS max7,
min((t.t8 / l.activeusers) * 100) AS min8, max((t.t8 / l.activeusers) * 100) AS max8,
min((t.t9 / l.activeusers) * 100) AS min9, max((t.t9 / l.activeusers) * 100) AS max9,
min((t.t10 / l.activeusers) * 100) AS min10, max((t.t10 / l.activeusers) * 100) AS max10,
min((t.t11 / l.activeusers) * 100) AS min11, max((t.t11 / l.activeusers) * 100) AS max11,
min((t.t12 / l.activeusers) * 100) AS min12, max((t.t12 / l.activeusers) * 100) AS max12
FROM leagues_daily l, training t WHERE l.leagueid = t.leagueid AND l.activeusers >=$minactive AND l.activeusers <=$maxactive", $dbh);

$minmax = mysql_fetch_array($minmaxes);

$query = "SELECT l.leagueid, l.activeusers, l.shortname, l.season, t.*,
(t.t0 / l.activeusers)*100 AS p0,

(t.t2 / l.activeusers)*100 AS p2,
(t.t3 / l.activeusers)*100 AS p3,
(t.t4 / l.activeusers)*100 AS p4,
(t.t5 / l.activeusers)*100 AS p5,
(t.t6 / l.activeusers)*100 AS p6,
(t.t7 / l.activeusers)*100 AS p7,
(t.t8 / l.activeusers)*100 AS p8,
(t.t9 / l.activeusers)*100 AS p9,
(t.t10 / l.activeusers)*100 AS p10,
(t.t11 / l.activeusers)*100 AS p11,
(t.t12 / l.activeusers)*100 AS p12
FROM leagues_daily l, training t
WHERE l.leagueid = t.leagueid AND (NOT (t0 = 0 AND t2 = 0 AND t3 = 0 AND t4 = 0 AND t5 = 0 AND t6 = 0 AND t7 = 0 AND t8 = 0 AND t9 = 0 AND t10 = 0 AND t11 = 0 AND t12 = 0)) AND l.activeusers >=$minactive AND l.activeusers <=$maxactive ";

if ($col != -1) {
	if ($col == "s") {$query = $query . "ORDER BY l.season DESC, l.activeusers DESC";}
	else if ($col == "id") {$query = $query . "ORDER BY l.leagueid";}
	else {$query = $query . "ORDER BY p$col $order, l.activeusers ASC";}
} else {$query = $query . "ORDER BY l.activeusers $order";}
$trainings = mysql_query($query, $dbh); 

echo "<TABLE border=1 WIDTH=\"1200\">\n\t<TR><TD><B>Country</B></TD><TD><B>Active</B></TD><TD><B><A HREF=\"training.php?c=s&r=$r&yb={$_GET['yb']}&min=$minactive&max=$maxactive\">Season</A></B></TD>";
for ($i = 0; $i <= 12; $i++) {
	if ($i == 1) continue;
	if ($col == $i &! $_GET['r']) { $r = 1; } else { $r = 0; } 
	echo "<TD><B><A HREF=\"training.php?c=$i&r=$r&yb={$_GET['yb']}&min=$minactive&max=$maxactive\">{$trainingtypes[$i]}</A></B></TD>";
}
if (!$_GET['dump']) {
	echo "</TR>\n";
	echo "<TR><TD>Globally</TD><TD>{$global['activeusers']}</TD><TD>25</TD>";
	for ($i = 0; $i <= 12; $i++) {
		if ($i == 1) continue;
		$num = $global["t$i"];
		$perc = $global["p$i"];
		printcell ($minmax["min$i"], $minmax["max$i"], $global["p$i"], $num, $perc, $i, $col, $_GET['yb']);
	}
	echo "</TR>\n";
	echo "<TR><TD BGCOLOR=\"silver\" COLSPAN=\"15\">&nbsp;</TD></TR>";
}
$keeptraining = array();

while ($training = mysql_fetch_array($trainings)) {
	if ($_GET['dump']) {
		for ($i = 0; $i <= 12; $i++) {
			if ($i == 1) continue;
			$num = $training["t$i"];
			$perc = $training["p$i"];
			if ($training["p$i"] != 0) {
				$sigma[$training['leagueid']][$i] = log($training["p$i"] / $global["p$i"], 2);
			} else {
				$sigma[$training['leagueid']][$i] = "-inf";
			}
			$keeptraining[$training['leagueid']] = $training;
		}
	} else {
		echo "\t<TR><TD>{$training['shortname']}</TD><TD>{$training['activeusers']}</TD><TD>{$training['season']}</TD>";
		for ($i = 0; $i <= 12; $i++) {
			if ($i == 1) continue;
			$num = $training["t$i"];
			$perc = $training["p$i"];
			printcell ($minmax["min$i"], $minmax["max$i"], $global["p$i"], $num, $perc, $i, $col, $_GET['yb']);
		}
	        echo "</TR>\n";
	}
}

function printcell ($min, $max, $avg, $num, $perc, $i, $col, $alternate) {
	
	$neg = $avg - $min;
	$pos = $max - $avg;
	
	if ($col == "s") $col = -1;

	if ($neg > $pos) {
		$max = $avg + $neg;
	} else {
		$min = $avg - $pos;
	}
	
	$factor = 254/($max - $min);
	$color = round(($perc-$min)*$factor);
	
	$blue = 0;
	$red = 2*$color - 255;
	$green = 255 - 2*$color;

	if ($red <= 0) {
		$red = 0;
		$blue = 255-$green;
	}
	if ($green <= 0) {
		$green = 0;
		$blue = 255-$red;
	}
	$newred = 255-$green;
	$green = 255-$red;
	$red = $newred;

	if ($alternate) {
		$blue = $red;
		$red = $green;
	}

	$hexcolor = sprintf("%02X%02X%02X", $red, $green, $blue);
	echo "<TD CLASS=\"";
	if ($red < 120 && $green < 120) {
		echo "darkback ";
	}
	if ($col == $i) {
		echo "selected";	
	}	
	echo "\" BGCOLOR=\"#$hexcolor\">$num<BR/>($perc%)</TD>";
}


if ($_GET['dump']) {
	$rho = array();
	$ssq = array();
	foreach (array_keys($sigma) as $leagueid) {
		$N = 0;
		$sx = 0;
		$sx2 = 0;
		foreach (array_keys($sigma[$leagueid]) as $type) {
			if ($sigma[$leagueid][$type] != "-inf") {
				$N++;
				$sx+=$sigma[$leagueid][$type];
				$sx2+=$sigma[$leagueid][$type]*$sigma[$leagueid][$type];
			}
		}
		$ssq[$leagueid] = $sx2 - (($sx*$sx)/$N);
	}
	foreach (array_keys($sigma) as $leagueid1) {
		foreach (array_keys($sigma) as $leagueid2) {
			if ($leagueid1 != $leagueid2) {
				$N = 0;
				$sx = 0;
				$sy = 0;
				$sxy = 0;
				for ($i = 0; $i <=12; $i++) {
					if ($i == 1) continue;
					if ($sigma[$leagueid1][$i] != "-inf" && $sigma[$leagueid2][$i] != "-inf") {
						$N++;
						$sx += $sigma[$leagueid1][$i];
						$sy += $sigma[$leagueid2][$i];
						$sxy += $sigma[$leagueid1][$i]*$sigma[$leagueid2][$i];
					}
				}
				if ($N == 0 || $ssq[$leagueid1] == 0 || $ssq[$leagueid2] == 0) {
					$rho[$leagueid1][$leagueid2] = 0;
				} else {
					$spr = $sxy - (($sx*$sy)/$N);
					$rho[$leagueid1][$leagueid2] = $spr / sqrt($ssq[$leagueid1]*$ssq[$leagueid2]);
				}
			}
		} 
	}
	
	$names = array();
	$namesquery = mysql_query("SELECT LeagueID, ShortName FROM leagues_daily", $dbh);
	while ($name = mysql_fetch_array($namesquery)) {
		$names[$name['LeagueID']] = $name['ShortName'];
	}
	$merges = array();
	foreach (array_keys($rho) as $key) {
		$merges[$key] = $names[$key];
	}

	while(count($rho) > 1) {
		$i = 0;
		$maxr = -2;
		$maxid1 = 0;
		$maxid2 = 0;
		foreach (array_keys($rho) as $id1) {
			foreach (array_keys($rho[$id1]) as $id2) {
				if ($rho[$id1][$id2] > $maxr) {
					$maxr = $rho[$id1][$id2];
					$maxid1 = $id1;
					$maxid2 = $id2;
				}
			}
		}
		$merges["$maxid1-$maxid2"][$maxid1] = $merges[$maxid1];
		$merges["$maxid1-$maxid2"][$maxid2] = $merges[$maxid2];
		$merges["$maxid1-$maxid2"]['r'] = $rho[$maxid1][$maxid2];

		unset($merges[$maxid1]);
		unset($merges[$maxid2]);

		foreach (array_keys($sigma[$maxid1]) as $type) {
			if ($sigma[$maxid1][$type] == "-inf") $sigma[$maxid1][$type] = $sigma[$maxid2][$type];
			if ($sigma[$maxid2][$type] == "-inf") $sigma[$maxid2][$type] = $sigma[$maxid1][$type];
			if ($sigma[$maxid1][$type] != "-inf" && $sigma[$maxid2][$type] != "-inf") {
				$sigma["$maxid1-$maxid2"][$type] = ($sigma[$maxid1][$type] + $sigma[$maxid2][$type]) / 2;
			} else {
				$sigma["$maxid1-$maxid2"][$type] = "-inf";
			}
		}
		
		unset($sigma[$maxid1]);
		unset($sigma[$maxid2]);
		unset($ssq[$maxid1]);
		unset($ssq[$maxid2]);

		$N = 0;
		$sx = 0;
		$sx2 = 0;
		foreach (array_keys($sigma["$maxid1-$maxid2"]) as $type) {
			if ($sigma["$maxid1-$maxid2"][$type] != "-inf") {
				$N++;
				$sx+=$sigma["$maxid1-$maxid2"][$type];
				$sx2+=$sigma["$maxid1-$maxid2"][$type]*$sigma["$maxid1-$maxid2"][$type];
			}
		}
		$ssq["$maxid1-$maxid2"] = $sx2 - (($sx*$sx)/$N);

        	unset($rho[$maxid1]);
		unset($rho[$maxid2]);
		
		foreach (array_keys($sigma) as $leagueid1) {
			unset($rho[$leagueid1][$maxid1]);
			unset($rho[$leagueid1][$maxid2]);
			foreach (array_keys($sigma) as $leagueid2) {
				if (($leagueid1 == "$maxid1-$maxid2" || $leagueid2 == "$maxid1-$maxid2") && $leagueid1 != $leagueid2) {
					$N = 0;
					$sx = 0;
					$sy = 0;
					$sxy = 0;
					for ($i = 0; $i <=12; $i++) {
						if ($i == 1) continue;
						if ($sigma[$leagueid1][$i] != "-inf" && $sigma[$leagueid2][$i] != "-inf") {
							$N++;
							$sx += $sigma[$leagueid1][$i];
							$sy += $sigma[$leagueid2][$i];
							$sxy += $sigma[$leagueid1][$i]*$sigma[$leagueid2][$i];
						}
					}
					if ($N == 0 || $ssq[$leagueid1] == 0 || $ssq[$leagueid2] == 0) {
						$rho[$leagueid1][$leagueid2] = 0;
					} else {
						$spr = $sxy - (($sx*$sy)/$N);
						$rho[$leagueid1][$leagueid2] = $spr / sqrt($ssq[$leagueid1]*$ssq[$leagueid2]);
					}
				}
			} 
		}
	}
	echo "<TD COLSPAN=\"8\" WIDTH=\"400\"><B><CENTER>Pearson Correlation Coefficient</CENTER></B></TD></TR>\n";

	echo "<TR><TD>Globally</TD><TD>{$global['activeusers']}</TD><TD>25</TD>";

	for ($i = 0; $i <= 12; $i++) {  
		if ($i == 1) continue;
		$num = $global["t$i"];
		$perc = $global["p$i"];
		printcell ($minmax["min$i"], $minmax["max$i"], $global["p$i"], $num, $perc, $i, $col, $_GET['yb']);
	}
	echo "<TD WIDTH=\"50\">1.00</TD><TD WIDTH=\"50\">0.75</TD><TD WIDTH=\"50\">0.50</TD><TD WIDTH=\"50\">0.25</TD><TD WIDTH=\"50\">0</TD><TD WIDTH=\"50\">-0.25</TD><TD WIDTH=\"50\">-0.50</TD><TD WIDTH=\"50\">-0.75</TD></TR>\n";
	echo "<TR><TD BGCOLOR=\"silver\" COLSPAN=\"15\">&nbsp;</TD><TD BGCOLOR=\"silver\" COLSPAN=\"8\" WIDTH=\"400\">&nbsp;</TD></TR>";
	treecut($merges, $keeptraining, $global, $minmax);
}

function getdepth($tree, $curdepth) {
	$maxdepth = $curdepth;
	if (!is_array($tree)) return $curdepth;
	foreach (array_keys($tree) as $key) {
		$depth = getdepth($tree[$key],$curdepth+1);
		if ($depth > $maxdepth) $maxdepth=$depth;
	}
	return $maxdepth;
}

function maketree($tree, $cumpx) {

	$widthfactor = 200;

	$keys = array_keys($tree);
	$keys1 = explode("-", $keys[0]);
	$keys2 = explode("-", $keys[1]);
	$rows1 = count($keys1);
	$rows2 = count($keys2);
	$r = $tree['r'];
	$barlength = round($widthfactor* (1 - $r), 0);
	$cells1 = array();
	$cells2 = array();
	$sig = 0;
	if ($r >= 0.78) $sig = 1;
	
	if ($rows1 == 1) {
		$cells1[$keys[0]] = "<IMG SRC=\"hattrick/g/{$sig}0.png\" WIDTH=\"$barlength\" HEIGHT=\"2\"/ CLASS=\"hor\"><IMG SRC=\"hattrick/g/{$sig}0.png\" WIDTH=\"2\" HEIGHT=\"33\" CLASS=\"ver\" STYLE=\"left: {$barlength}px\">";
	} else {
		$tweenbar1 = abs(round($widthfactor*($r - $tree[$keys[0]]['r']), 0));
		$newcumpx = $cumpx + $barlength;
		$cells1 = maketree($tree[$keys[0]], $newcumpx);
		$cells1[$keys1[0]] = $cells1[$keys1[0]] . "<IMG SRC=\"hattrick/g/{$sig}0.png\" WIDTH=\"$tweenbar1\" HEIGHT=\"2\"/ CLASS=\"ver\" STYLE=\"left: ". ($barlength - $tweenbar1) . "px\"><IMG SRC=\"hattrick/g/{$sig}0.png\" WIDTH=\"2\" HEIGHT=\"" . (($rows1*31) + 2) . "\" CLASS=\"ver\" STYLE=\"left: " . ($barlength) . "px\">";
	}

	if ($rows2 == 1) {
		$cells2[$keys[1]] = "<IMG SRC=\"hattrick/g/{$sig}0.png\" WIDTH=\"$barlength\" HEIGHT=\"2\"/>";
	} else {
		$tweenbar2 = abs(round($widthfactor*($r - $tree[$keys[1]]['r']), 0)) + 1;
		$newcumpx = $cumpx + $barlength;
		$cells2 = maketree($tree[$keys[1]], $newcumpx);
		$cells2[$keys2[0]] = $cells2[$keys2[0]] . "<IMG SRC=\"hattrick/g/{$sig}0.png\" WIDTH=\"$tweenbar2\" HEIGHT=\"2\"/ CLASS=\"ver\" STYLE=\"left: ". ($barlength - $tweenbar2) . "px\">";
	}

	$cells = array();
	$cells = $cells1 + $cells2;
	return ($cells);
}

function treecut ($tree, $training, $global, $minmax) {
	$rootkey = current(array_keys($tree));
	$keys = explode("-", $rootkey);
	$height = count($keys);
	
	$legkeys = array_keys($tree[$rootkey]);
	$leg1 = $tree[$rootkey][$legkeys[0]];
	$leg2 = $tree[$rootkey][$legkeys[1]];
	
	$treecells = maketree($tree[$rootkey], 0);
	foreach ($keys as $key) {
		echo "\t<TR><TD>{$training[$key]['shortname']}</TD><TD>{$training[$key]['activeusers']}</TD><TD>{$training[$key]['season']}</TD>";
		for ($i = 0; $i <= 12; $i++) {
			if ($i == 1) continue;
			$num = $training[$key]["t$i"];
			$perc = $training[$key]["p$i"];
			printcell ($minmax["min$i"], $minmax["max$i"], $global["p$i"], $num, $perc, $i, -1, $_GET['yb']);
		}
		$cell = "&nbsp;";
		if (isset($treecells[$key])) $cell = $treecells[$key];
		echo "<TD WIDTH=\"50\"><DIV>$cell</DIV></TD><TD WIDTH=\"50\">&nbsp;</TD><TD WIDTH=\"50\">&nbsp;</TD><TD WIDTH=\"50\">&nbsp;</TD><TD WIDTH=\"50\">&nbsp;</TD><TD WIDTH=\"50\">&nbsp;</TD><TD WIDTH=\"50\">&nbsp;</TD><TD WIDTH=\"50\">&nbsp;</TD>";
	        echo "</TR>\n";
	}
}

?>

</TABLE>
Number of active users in each country is updated each day; training only each week. This may cause discrepancies between the number of active users in a league and the sum of users over all training types.
<PRE>
</PRE>
<?php urchin(); ?>
</BODY>
</HTML>

